package guoanmaker.provider.business.service;

import guoanmaker.provider.business.model.PersonalUser;
import guoanmaker.provider.business.model.ProvideShop;
import guoanmaker.provider.business.model.ShopCommodity;
import guoanmaker.provider.business.modelnobind.Appraise;
import guoanmaker.provider.business.modelnobind.Business2lvCount;
import guoanmaker.provider.business.modelnobind.ProvideShopDetailMove;
import guoanmaker.provider.business.modelnobind.ShopCity;
import guoanmaker.provider.business.repository.PersonalUserRepository;
import guoanmaker.provider.business.repository.ProvideShopRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.*;

/**
 * DESC:
 * Created by JTing on 2017/12/15 10:27
 */
@Service
public class ProvideShopService implements ProvideShopServiceInterface {

    @Autowired
    private ProvideShopRepository provideShopRepository;

    @PersistenceContext
    private EntityManager em;
    @Autowired
    private PersonalUserRepository personalUserRepository;

    /* *
     * @Description:新增加商铺
     * @author JTing
     * @date 2017/12/15 11:17
     * @param [provideShop]
     * @return guoanmaker.provider.business.model.ProvideShop
     */
    @Override
    public ProvideShop saveProvideShop(ProvideShop provideShop) {
        ProvideShop ps = provideShopRepository.save(provideShop);
        return null;
    }

    /* *
     * @Description:查询店铺资料
     * @author JTing
     * @date 2017/12/15 11:17
     * @param [businessId]
     * @return java.util.Map
     */
    @Override
    public Map<String, Object> selectProvideShop(String businessId) {
        Map<String, Object> map = new HashMap<>();
        ProvideShop shop = provideShopRepository.findShop(businessId);
//        System.out.println(shop);
        if (shop == null) {
            map.put("result", null);
            return map;
        }
        shop.setProviderBusiness(null);
        map.put("result", shop);
        return map;

    }

    /* *
     * @Description:根据店铺id查询店铺资料
     * @author JTing
     * @date 2017/12/15 11:17
     * @param [businessId]
     * @return java.util.Map
     */
    @Override
    public Map<String, Object> selectProvideShopByShopId(String shopId) {
        Map<String, Object> map = new HashMap<>();
        ProvideShop shop = provideShopRepository.findOne(shopId);
        if (shop == null) {
            map.put("result", null);
            return map;
        }
        shop.setProviderBusiness(null);
        map.put("result", shop);
        return map;
    }

    /* *
     * @Description: 商品详情(商户id)
     * @author JTing
     * @date 2017/12/17 14:25
     * @param [businessId]
     * @return java.lang.Integer
     */
    @Override
    public Map<String, Object> shopDetailByBid(String businessId) {

        Map<String, Object> map = new HashMap<>();
        //先判断business状态
        //获取用户id
        String sql = "select personal_user_id from  t_provide_business where id='" + businessId + "'";
        Query qryforuid = em.createNativeQuery(sql);
        List res = qryforuid.getResultList();
        String uid = res.get(0).toString();
        PersonalUser user = personalUserRepository.findOne(uid);
        boolean equals = user.getIsProvider() != null && user.getIsProvider().equals("2");
        if (!equals) {
            map.put("error", "服务商用户状态不正常");
            return map;
        }
        ProvideShop shop = provideShopRepository.findShop(businessId);
        if (shop == null) {
            map.put("error", "不存在店铺");
            return map;
        }
        String jpql = "select new guoanmaker.provider.business.modelnobind.ProvideShopDetailMove(ps.providerBusiness.id,ps.id,ps.businessName" +
                ", ps.companyLogo,ps.followNum,ps.state,ps.banner,ps.introduce,ps.companyLicenseReplica,ps.bannerForPc) from ProvideShop ps where ps.providerBusiness.id='" + businessId + "'";


        if (equals) {
            if (shop.getState() != 1) {
                jpql = "select new guoanmaker.provider.business.modelnobind.ProvideShopDetailMove(ps.providerBusiness.id,ps.id,pb.businessName" +
                        ", pb.companyLogo,ps.followNum,ps.state,pb.banner,pb.introduce,pb.companyLicenseReplica,pb.bannerForPc) from ProvideShop ps   left join ps.providerBusiness pb where ps.providerBusiness.id='" + businessId + "'";
            }

        }

        Query query = em.createQuery(jpql);

        List<ProvideShopDetailMove> resultList = query.getResultList();
        if (resultList.size() == 0) {
            map.put("error", "没有对应的商户");
            return map;
        }
        ProvideShopDetailMove shopDtl = resultList.get(0);
        shopDtl.setScore(getProvideShopScore(businessId));
        map.put("shopDtl", shopDtl);
        return map;
    }


    /* *
     * @Description:修改店铺资料
     * @author JTing
     * @date 2017/12/15 14:32
     * @param [provideShop]
     * @return java.lang.Integer
     */
    @Override
    public Integer modProvideShop(ProvideShop provideShop) {
        String id = provideShop.getId();
        ProvideShop ps = provideShopRepository.findOne(id);
        if (ps == null) {
            return 0;
        }
        if (provideShop.getBusinessName() != null) {
            ps.setBusinessName(provideShop.getBusinessName());
        }
        if (provideShop.getLicensPhone() != null) {
            ps.setLicensPhone(provideShop.getLicensPhone());
        }
        if (provideShop.getCompanyAdress() != null) {
            ps.setCompanyAdress(provideShop.getCompanyAdress());
        }
        if (provideShop.getCompanyProvince() != null) {
            ps.setCompanyProvince(provideShop.getCompanyProvince());
        }
        if (provideShop.getCompanyArea() != null) {
            ps.setCompanyArea(provideShop.getCompanyArea());
        }
        if (provideShop.getCompanyCity() != null) {
            ps.setCompanyCity(provideShop.getCompanyCity());
        }
        if (provideShop.getCompanyLogo() != null) {
            ps.setCompanyLogo(provideShop.getCompanyLogo());
        }
        if (provideShop.getCompanyLicenseReplica() != null) {
            ps.setCompanyLicenseReplica(provideShop.getCompanyLicenseReplica());
        }
        if (provideShop.getBanner() != null) {
            ps.setBanner(provideShop.getBanner());
        }
        if (provideShop.getIntroduce() != null) {
            ps.setIntroduce(provideShop.getIntroduce());
        }
        if (provideShop.getBannerForPc() != null) {
            ps.setBannerForPc(provideShop.getBannerForPc());
        }
        if (provideShop.getCal() != null) {
            ps.setCal(provideShop.getCal());
        }
        //update the state and the statetime
        ps.setState(0);
        ps.setStateTime(new Date());
        provideShopRepository.save(ps);

        return 1;
    }

    /* *
     * @Description:获得商户得分
     * @author JTing
     * @date 2017/12/15 14:14
     * @param [businessId]
     * @return java.lang.Double
     */
    @Override
    public String getProvideShopScore(String businessId) {

        String sql = "select avg(a.average_value) from t_personal_appraise a, t_provide_commodity b where a.is_show='1' and a.commodity_id=b.id  and b.business_id='" + businessId + "'";
        Query navQry = em.createNativeQuery(sql);
        if (navQry.getResultList().get(0) == null) {
            return "暂无评分";
        }
        Double score = Double.parseDouble(navQry.getResultList().get(0) + "");
        String format = String.format("%.1f", score);
        return format;
    }

    @Override
    public Map<String, Object> getProvideShopScoreDtl(String businessId) {
        Map<String, Object> map = new HashMap<>();
        String sql = "select a.info from t_personal_appraise a, t_provide_commodity b where  a.is_show='1' and  a.commodity_id=b.id  and b.business_id='" + businessId + "'";
        Query navQry = em.createNativeQuery(sql);
        List res = navQry.getResultList();
        if (res.size() == 0) {
            map.put("score", "暂无评分");
            return map;
        }

        Double sum = 0.0;
        for (Object ss : res
                ) {
            sum += Double.parseDouble(ss.toString().split(",")[0].substring(5));
        }
        map.put("gtxq", String.format("%.1f", sum / res.size()));

        sum = 0.0;
        for (Object ss : res
                ) {
            sum += Double.parseDouble(ss.toString().split(",")[1].substring(5));
        }
        map.put("fwxl", String.format("%.1f", sum / res.size()));

        sum = 0.0;
        for (Object ss : res
                ) {
            sum += Double.parseDouble(ss.toString().split(",")[2].substring(5));
        }
        map.put("wczl", String.format("%.1f", sum / res.size()));

        map.put("score", getProvideShopScore(businessId));
        return map;

    }

    @Override
    public Map<String, Object> getBusinessAllAppraise(String businessId, Integer page, Integer pageSize, Integer type) {
        Map<String, Object> map = new HashMap<>();
        String jpqlori = "select new guoanmaker.provider.business.modelnobind.Appraise(c.commodityName,a.averageValue,"
                + "u.nickname,a.appraiseTime,a.appraise,a.info,u.portrait,u.id,a.reply,a.replyTime,u.username) from PersonalAppraise a left join a.orderForm o left join o.user u "
                + "left join o.commodity c left join c.providerBusiness b where a.isShow='1' and b.id='" + businessId + "' ";
        String jpql = jpqlori;
        if (type == null || type == 0) {
            jpql = jpqlori + " order by a.appraiseTime desc";
        }
        if (type != null && type == 1) {
            jpql = jpqlori + " and a.averageValue >4 order by a.appraiseTime desc";
        }
        if (type != null && type == 2) {
            jpql = jpqlori + " and a.averageValue <=4 and a.averageValue>2 order by a.appraiseTime desc";
        }
        if (type != null && type == 3) {
            jpql = jpqlori + " and a.averageValue <=2 order by a.appraiseTime desc";

        }
        Query query = em.createQuery(jpql);
        List<Appraise> resultList = query.getResultList();
        for (Appraise a: resultList
             ) {
            if (a.getUsername().length()>=10) {
            }
            a.setUsername(a.getUsername().replace(a.getUsername().substring(3,3+4),"****"));
        }
        String sql = "select sum( case when  a.average_value>4 then 1 else  0 end ),sum( case when  a.average_value<=4 and average_value >2 then 1 else  0 end ),sum( case when  a.average_value<=2 then 1 else  0 end )  " +
                " from t_personal_appraise a, t_provide_commodity b where  a.is_show='1' and  a.commodity_id=b.id  and b.business_id='" + businessId + "'";
        Query nativeQuery = em.createNativeQuery(sql);
        List resultList1 = nativeQuery.getResultList();
        if (resultList1.size() > 0) {
            for (Object row : resultList1
                    ) {
                Object[] cells = (Object[]) row;
                if (cells[0] != null) {
                    map.put("haopingshu", Integer.parseInt(cells[0].toString()));
                }
                if (cells[1] != null) {
                    map.put("zhongpingshu", Integer.parseInt(cells[1].toString()));
                }
                if (cells[2] != null) {
                    map.put("chapingshu", Integer.parseInt(cells[2].toString()));
                }
                if (cells[0] != null && cells[1] != null && cells[2] != null) {
                    map.put("zongpingshu", Integer.parseInt(cells[0].toString()) + Integer.parseInt(cells[1].toString()) + Integer.parseInt(cells[2].toString()));
                }
            }
        }

        if (page == null || pageSize == null) {
            map.put("appraise", resultList);
        }
        if (page != null && pageSize != null) {
            query.setFirstResult(page * pageSize);
            query.setMaxResults(pageSize);
            resultList = query.getResultList();
            for (Appraise a: resultList
                    ) {
                if (a.getUsername().length()>=10) {
                }
                a.setUsername(a.getUsername().replace(a.getUsername().substring(3,3+4),"****"));
            }
            map.put("appraise", resultList);
        }
        return map;

    }

    @Override
    public Map<String, Object> shopCommClass(String businessId, String cityId) {
        Map<String, Object> map = new HashMap<>();

        String sql = "select getsidfor1or2(t.service_type_id,2) sid ,getsnamefor1or2(t.service_type_id,2) sname ," +
                "count(1) from t_provide_commodity t  left join " +
                "(select commodity_id,city_id,area_id,count( city_id),count(commodity_id) from t_provide_commodity_service_range   group by city_id ,commodity_id) b" +
                " on t.id=b.commodity_id where t.commodity_state=2 and t.type<>'4' and t.business_id='" + businessId + "' group by sid,sname ";
        if (cityId != null) {
            sql = "select getsidfor1or2(t.service_type_id,2) sid ,getsnamefor1or2(t.service_type_id,2) sname ," +
                    "count(1) from t_provide_commodity t left join " +
                    " (select commodity_id,city_id,area_id,count( city_id),count(commodity_id) from t_provide_commodity_service_range   group by city_id ,commodity_id) r  " +
                    "on t.id=r.commodity_id where (r.city_id='" + cityId + "' or r.city_id is null) and " +
                    "t.commodity_state=2 and  t.type<>'4' and t.business_id='" + businessId + "' group by sid,sname ";
        }
        Query qry = em.createNativeQuery(sql);
        List res = qry.getResultList();
        if (res.size() == 0) {
            map.put("class", null);
            return map;
        }
        ArrayList<Business2lvCount> arr = new ArrayList<>();
        for (Object row : res
                ) {
            Object[] cells = (Object[]) row;
            arr.add(new Business2lvCount(cells[0].toString(), cells[1].toString(), Integer.parseInt(cells[2].toString())));
        }
        map.put("class", arr);
        return map;

    }

    @Override
    public Map<String, Object> findShopComCity(String businessId) {
        Map<String, Object> map = new HashMap<>();
        String sql = "select c.id,c.name from t_provide_commodity_service_range a, t_provide_commodity b,t_provide_city c  " +
                "where a.commodity_id=b.id and a.city_id=c.id and b.commodity_state=2 and  b.type<>'4' and b.business_id='" + businessId + "' group by c.id,c.name order by c.id";
        Query qry = em.createNativeQuery(sql);
        List res = qry.getResultList();
        ArrayList<ShopCity> arr = new ArrayList<>();
        if (res.size() == 0) {
            map.put("citys", arr);
            return map;
        }

        for (Object row : res
                ) {
            Object[] cells = (Object[]) row;
            arr.add(new ShopCity(cells[0].toString(), cells[1].toString()));
        }
        map.put("citys", arr);
        return map;

    }

    @Override
    public Map<String, Object> findShopComm(String businessId, String sid, String cityId, Integer page, Integer pageSize, Integer sortType) {

        Map<String, Object> map = new HashMap<>();
        ArrayList<ShopCommodity> arr = new ArrayList<>();

        //没有区域限制的处理
        String forChinaComm = "select id,commodity_code,commodity_name,commodity_bigimage,commodity_smallimage,synopsis,final_price,'全国' as city_name," +
                "type,'0' as city_id,getsidfor1or2(service_type_id,2), is_bargaining from t_provide_commodity where commodity_state=2 and type<>'4' and is_territory_restriction='2' and business_id='" + businessId + "'";
        Query qryforChinaComm = em.createNativeQuery(forChinaComm);
        List res = qryforChinaComm.getResultList();
        for (Object row : res
                ) {
            Object[] cells = (Object[]) row;
            arr.add(new ShopCommodity((String) cells[0], cells[1].toString(),(String) cells[2], (String)cells[3], (String)cells[4],
                    (String)cells[5], Double.parseDouble(cells[6].toString()), (String)cells[7], (String)cells[8],(String) cells[9], (String)cells[10],(String)cells[11]));
        }
        //有区域限制的处理
        String forareaComm = "select a.id,a.commodity_code,a.commodity_name,a.commodity_bigimage,a.commodity_smallimage,a.synopsis,b.final_price,c.name,a.type,c.id as city_id,getsidfor1or2(a.service_type_id,2),b.area_id,b.id as rid , c.code,a.is_bargaining " +
                "from t_provide_commodity a ,t_provide_commodity_service_range b,t_provide_city c where a.id=b.commodity_id  and b.city_id=c.id and  " +
                "a.is_territory_restriction=1 and a.commodity_state=2 and a.type<>'4' and a.business_id='" + businessId + "'";
        Query qryForArea = em.createNativeQuery(forareaComm);
        List resAdd = qryForArea.getResultList();
        for (Object row : resAdd
                ) {
            Object[] cells = (Object[]) row;
            arr.add(new ShopCommodity((String)cells[0], cells[1].toString(),(String) cells[2],(String) cells[3], (String)cells[4],
                    (String)cells[5], Double.parseDouble(cells[6].toString()), (String)cells[7],(String) cells[8], (String)cells[9], (String)cells[10], (String)cells[12], (String)cells[13],(String)cells[14]));
        }
        //remove the equals entity (cityid and id is the same)
        LinkedHashSet<ShopCommodity> linkedHashSet = new LinkedHashSet<>();
        for (ShopCommodity s: arr
             ) {
            linkedHashSet.add(s);
        }
        arr = new ArrayList<>(linkedHashSet);
        if (cityId != null) {
            Collections.reverse(arr);
        }
        //处理二级服务分类
        if (sid != null) {
            Iterator<ShopCommodity> it = arr.iterator();
            while (it.hasNext()) {
                ShopCommodity next = it.next();
                if (!next.getSid().equals(sid)) {
                    it.remove();
                }
            }
        }

        //处理区域
        if (cityId != null) {
            Iterator<ShopCommodity> it = arr.iterator();
            while (it.hasNext()) {
                ShopCommodity next = it.next();
                if ((!next.getCityId().equals(cityId)) && (!next.getCityId().equals("0"))) {
                    it.remove();
                }
            }
        }
        //处理排序
        if (sortType != null && sortType == 0) {
            //从小到大
            Collections.sort(arr);
        }
        if (sortType != null && sortType == 1) {
            //从大到小
            Collections.sort(arr);
            Collections.reverse(arr);
        }
        map.put("total", arr.size());
        // 处理分页
        if (arr.size() > 0) {
            if (page != null && pageSize != null) {
                Integer star = page * pageSize;
                Integer lastPage = page * pageSize + pageSize;
                if (star >= arr.size() - 1) {
                    star = arr.size() - 1;
                }
                if (lastPage >= arr.size()) {
                    lastPage = arr.size();
                }
                List<ShopCommodity> shopCommodities = arr.subList(star, lastPage);


                map.put("comm", shopCommodities);
                return map;
            }
        }


        map.put("comm", arr);
        return map;
    }

}
